USE SAS
GO

 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'SearchAccounts')
	BEGIN
		DROP  Procedure  dbo.SearchAccounts
	END

GO

CREATE Procedure dbo.SearchAccounts
	@text		VARCHAR(max)
,	@userId		VARCHAR(10)
AS
BEGIN

	DECLARE 
		@P_CATEGORY INT
	,	@P_TYPE		INT
	,	@R_OWNER	INT

	SELECT
		@P_CATEGORY = 2
	,	@P_TYPE		= 2
	,	@R_OWNER	= 3


	;WITH A AS (
		-- SEARCH IN ACCOUNT
		SELECT
			a.PartnerNo		AS AccountNo
		,	a.PartnerName	AS AccountName
		,	a.PartnerGUID	AS PKID -- AccountGUID
		FROM TR_PARTNER A WITH (NOLOCK)
		INNER JOIN TR_RELATIONSHIPS AS R WITH (NOLOCK)
			ON R.SourceGUID = a.PartnerGUID
		WHERE
			A.PartnerCategory	= @P_CATEGORY	-- ORGANIZATION
		AND A.PartnerType		= @P_TYPE		-- CHILD A/C
		AND	R.RelationShipType	= @R_OWNER
		AND R.Partner2 = @userID
		AND	A.PartnerName LIKE @text

		UNION
		-- SEARCH IN NOTES
		SELECT
			a.PartnerNo		-- Account No
		,	a.PartnerName	-- Account Name
		,	a.PartnerGUID	-- AccountGUID
		FROM TR_PARTNER A WITH (NOLOCK)
		INNER JOIN TR_NOTE AS N WITH (NOLOCK)
			ON N.SourceGUID = A.PartnerGUID
		INNER JOIN TR_RELATIONSHIPS AS R WITH (NOLOCK)
			ON R.SourceGUID = a.PartnerGUID
		WHERE
			A.PartnerCategory	= @P_CATEGORY	-- ORGANIZATION
		AND A.PartnerType		= @P_TYPE		-- CHILD A/C
		AND	R.RelationShipType	= @R_OWNER
		AND R.Partner2 = @userID
		AND	N.NoteText LIKE @text

		UNION
		-- SEARCH IN ADDRESS
		SELECT
			a.PartnerNo		-- Account No
		,	a.PartnerName	-- Account Name
		,	a.PartnerGUID	-- AccountGUID
		FROM TR_PARTNER A WITH (NOLOCK)
		INNER JOIN TR_ADDRESS AS Ad WITH (NOLOCK)
			ON Ad.PartnerGUID = A.PartnerGUID
		INNER JOIN TR_RELATIONSHIPS AS R WITH (NOLOCK)
			ON R.SourceGUID = a.PartnerGUID
		INNER JOIN TR_STATES AS S WITH (NOLOCK)
			ON S.State = Ad.State
		WHERE
			A.PartnerCategory	= @P_CATEGORY	-- ORGANIZATION
		AND A.PartnerType		= @P_TYPE		-- CHILD A/C
		AND	R.RelationShipType	= @R_OWNER
		AND R.Partner2 = @userID
		AND	(Ad.Street1 LIKE @text 
			OR Ad.Street2 LIKE @text 
			OR Ad.City LIKE @text
			OR Ad.State LIKE @text
			OR S.StateDesc LIKE @text)
	), B AS (
		-- PARENT ACCOUNT
		SELECT
			p.PartnerName		AS [ParentAccountName]
		,	A.PKID
		FROM TR_PARTNER as p WITH (NOLOCK)
		INNER JOIN TR_RELATIONSHIPS as r WITH (NOLOCK)
			ON r.Partner2 = p.PartnerNo
		INNER JOIN A 
			ON A.PKID = r.SourceGUID
		WHERE
			r.RelationshipType = 7 -- PARENT ACCOUNT
	), C AS (
		-- ADDRESS
		SELECT
			ad.Street1 + ', ' + ad.Street2	as [Street]
		,	ad.City
		,	ad.State
		,	ad.Zip
		,	ad.AddressGUID
		,	A.PKID
		FROM TR_ADDRESS as ad WITH (NOLOCK)
		INNER JOIN A 
			on A.PKID = ad.PartnerGUID
	), D AS (
		-- COMMUNICATION
		SELECT
			com.Phone 
		,	C.PKID
		FROM TR_COMMUNICATION as com WITH (NOLOCK)
		INNER JOIN C 
			on C.AddressGUID = com.AddressGUID
	), FINAL AS (
		SELECT
			A.AccountNo				AS [Account No]
		,	A.AccountName			AS [Account Name]
		,	B.ParentAccountName		AS [Parent Account]
		,	C.Street				AS [Street]
		,	C.City
		,	C.State
		,	D.Phone
		,	A.PKID
		FROM A
		LEFT OUTER JOIN B
			ON B.PKID = A.PKID
		LEFT OUTER JOIN C
			ON C.PKID = A.PKID
		LEFT OUTER JOIN D
			ON D.PKID = A.PKID
	)


	SELECT * FROM FINAL
	ORDER BY [Account No]
END
GO


-- SearchAccounts @userID = 3